﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Sicurezza.Entita;
using Sicurezza.CA.Entita;
using System.Data.OleDb;

namespace Sicurezza.CA.DataProvider
{
    public class RenewDataProvider
    {
        public int SaveRequest(string serial)
        {
            int ReqId = 0;
            using (OleDbConnection conn = new OleDbConnection(ConnectionStringDataProvider.ConnectionString))
            {
                string query = "INSERT INTO Renews " +
                               "([Timestamp],Serial,State,Reason) " +
                               "VALUES (#" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "#,'" + serial + "'," + (int)RequestState.Waiting + ",'')";

                OleDbCommand cmd = new OleDbCommand(query, conn);
                conn.Open();
                cmd.ExecuteNonQuery();

                cmd.CommandText = "SELECT @@Identity";
                ReqId = Convert.ToInt32(cmd.ExecuteScalar());
            }

            return ReqId;
        }

        public List<Request> GetWaiting()
        {
            List<Request> list;

            using (OleDbConnection conn = new OleDbConnection(ConnectionStringDataProvider.ConnectionString))
            {
                string query = "SELECT * " +
                               "FROM ((Renews r INNER JOIN Certificates c ON r.Serial = c.Id) " +
                               "INNER JOIN Profiles p ON c.ProfileId = p.Id) "+
                               "WHERE State=" + (int)RequestState.Waiting;

                OleDbCommand cmd = new OleDbCommand(query, conn);
                conn.Open(); ;

                list = BuildObjects(cmd.ExecuteReader());
            }

            return list;
        }

        private List<Request> BuildObjects(OleDbDataReader dr)
        {
            List<Request> reqs = new List<Request>();
            Request r = null;
            while (dr.Read())
            {
                r = new Request();
                r.Id = Convert.ToInt32(dr["r.Id"]);
                r.Subject = "C=" + Convert.ToString(dr["C"]);
                r.Subject += "; O=" + Convert.ToString(dr["O"]);
                r.Subject += "; OU=" + Convert.ToString(dr["OU"]);
                r.Subject += "; CN=" + Convert.ToString(dr["CN"]);
                r.Subject += "; Email= " + Convert.ToString(dr["Email"]);
                r.Timestamp = Convert.ToDateTime(dr["Timestamp"]);
                r.digitalSignature = Convert.ToBoolean(dr["DigitalSignature"]);
                r.keyEncipherment = Convert.ToBoolean(dr["KeyEncipherment"]);

                reqs.Add(r);


            }

            return reqs;
        }

        public int Get(string ReqId)
        {
            int serial = 0;

            using (OleDbConnection conn = new OleDbConnection(ConnectionStringDataProvider.ConnectionString))
            {
                string query = "SELECT serial " +
                               "FROM Renews  " +
                               "WHERE Id=" + ReqId;

                OleDbCommand cmd = new OleDbCommand(query, conn);
                conn.Open(); ;

                serial = Convert.ToInt32(cmd.ExecuteScalar());
            }

            return serial;
        }

        public void SetState(int ReqId, RequestState requestState)
        {
            SetState(ReqId, requestState, "");
        }

        public void SetState(int ReqId, RequestState requestState, string reason)
        {
            using (OleDbConnection conn = new OleDbConnection(ConnectionStringDataProvider.ConnectionString))
            {
                string query = "UPDATE Renews " +
                               "SET State=" + (int)requestState + ",Reason='" + reason + "' " +
                               "WHERE Id=" + ReqId;

                OleDbCommand cmd = new OleDbCommand(query, conn);
                conn.Open();
                cmd.ExecuteNonQuery();
            }
        }

        public string Check(int RenId)
        {
            string reason = null;

            using (OleDbConnection conn = new OleDbConnection(ConnectionStringDataProvider.ConnectionString))
            {
                string query = "SELECT * FROM Renews " +
                               "WHERE Id=" + RenId.ToString();

                OleDbCommand cmd = new OleDbCommand(query, conn);
                conn.Open();

                OleDbDataReader dr = cmd.ExecuteReader();

                if (dr.Read())
                {

                    RequestState rs = (RequestState)Convert.ToByte(dr["State"]);
                    if (rs == RequestState.Refused)
                        reason = Convert.ToString(dr["Reason"]);
                }
            }

            return reason;
        }
    }
}
